
var mysql  = require('mysql')
require('./function/function.js')
var fs = require("fs")

var ConnectionConfig = 
{
host: 'game1715.com', 
user:'t',
password : 's123',
port: '3306',
dateStrings: true,
multipleStatements: true
}

/////////数据结构配置/////////////////

var userDataForSql = ['id','name','gold']  //数据库是这个结构


/////////////////////////////////////////
//初始话数据库
exports.initMysql = function()
{	
	var env = this
	var connection = mysql.createConnection(ConnectionConfig)
	var sql1 = 'create database if not exists dbl;'
	var sql2 = 'create table if not exists dbl.user (' //表名取userData phpmyadmin莫名报错
	for(var i in userDataForSql)
	{	
		if (userDataForSql[i]=='id')
			sql2 = sql2 + '`id` int(11) primary key not null auto_increment'
		else
			sql2 = sql2 + '`' + userDataForSql[i] + '` text'
		if (i<userDataForSql.length-1) sql2 = sql2 + ','
	}
	sql2 = sql2 + ')ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;'

    connection.query(sql1, 
 	function(err, rows, fields) 
 	{ 
 		if(err){console.log(err.message);connection.end();return;} 	    
	    initDbl()
    })
	var initDbl = function()
	{
	 	connection.query(sql2, 
	 	function(err, rows, fields)
	 	{ 
				if(err){console.log(err.message);connection.end();return;} 	   
				_initProcedures(connection)
				env.updateUserGold()
				//connection.end() 
		})
	}
}

exports.updateUserGold = function()
{
	var connection = mysql.createConnection(ConnectionConfig)

	fs.readFile('server/updateUserGold.log', function(err, data)
	{	
		var data = data.toString()
		if(err || !data){
			console.log(err,data)
	        connection.end()
	        return
	    }  
    	console.log(data)
    	connection.query(data,
    	function (err, retsult) {
	        if(err){
	        	console.log(err)
	            connection.end()
	            return;
	        }
	        fs.writeFile('server/updateUserGold.log','')         
		    connection.end()
		})
    })

}

//当内存中无用户数据时 会去数据库拉取 有三种情况1、玩家socket退出重进  2、新用户 3、服务器宕机,内存数据丢失 
exports.getUserByNameAsyn = function(name, callBack)
{	
	var connection = mysql.createConnection(ConnectionConfig)

	var sql = 'call dbl.getUserByName(' + '"' + name + '"' +   ',@a,@q);'
	connection.query(sql,function (err, retsult) {
        if(err){
            console.log('[EXEC PROC ERROR] - ',err.message)
            callBack('getUserByNameAsyn:fail')
            connection.end()
            return;
        }        
    	console.log(retsult,retsult[0])
    	var ud = retsult[0][0]
    	console.log('21',ud)
    	//ud.state = 'free' // 这个是内存数据 如果宕机了 这个数据默认free
    	ud.roomId = 1000  
    	callBack(0, ud)
	    connection.end()
    })
}

////////////////////////////////////////////////////////////
_initProcedures= function(connection)
{
	connection = connection || mysql.createConnection(ConnectionConfig)
	//getUserByName
	var sql = 'CREATE PROCEDURE dbl.getUserByName(IN inName text, OUT outId int, OUT outGold text)	' + '\n' + 
	'BEGIN 																							' + '\n' + 
	'	declare outId int default null; 															' + '\n' + 
	'	declare outGold text default 100000; 														' + '\n' + 
	'	select id,gold into outId,outGold from user where name = inName;							' + '\n' + 
	'	if outId then 																				' + '\n' + 
	'    	select * from user where name = inName; 												' + '\n' + 
	'    else 																						' + '\n' + 
	'    	insert into user(id, name, gold) values(outId, inName, outGold); 						' + '\n' + 
	'    	while row_count()>0 do 																	' + '\n' + 
	'    		select * from user where name = inName; 											' + '\n' + 
	'    	end while; 																				' + '\n' + 
	'    end if; 																					' + '\n' + 
	'END' 
	connection.query(sql,function (err, retsult) {
        if(err){
            console.log('[_initProcedures error] - ',err.message);
            connection.end()
            return;
        }        
    	console.log(retsult)
    	connection.end()
    })
}
